﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Data;

namespace Cherish.LScheduler.Data.Sqlite
{
    public class SqliteManager
    {
        private static string DBName = AppDomain.CurrentDomain.BaseDirectory + "scheduler.db";
        public static void InitSqlite()
        {
            if (!File.Exists(DBName))
            {
                SQLiteConnection.CreateFile(DBName);
            }
            //创建数据库
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "select count(1) from sqlite_master where type='table' and name='activity'";
                    var flag = Convert.ToInt32(command.ExecuteScalar());
                    if (flag == 1)
                    {
                        return;
                    }
                    command.CommandText = "CREATE TABLE activity(id integer primary key autoincrement, subject varchar(30),content varchar(500),starttime timestamp,endtime timestamp,color varchar(7))";
                    command.ExecuteNonQuery();
                }
            }
        }

        private static SQLiteConnection GetConnection(bool isOpen = true)
        {
            SQLiteConnection connection = new SQLiteConnection();
            SQLiteConnectionStringBuilder connectionStringBuiler = new SQLiteConnectionStringBuilder();
            connectionStringBuiler.DataSource = DBName;
            connection.ConnectionString = connectionStringBuiler.ToString();
            if (isOpen)
            {
                connection.Open();
            }
            return connection;
        }

        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteInsert(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    //if (isAddTag)
                    //{
                    //    sql += " select last_insert_rowid()";
                    //}
                    command.CommandText = sql;
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    command.ExecuteNonQuery();
                    return Convert.ToInt32(connection.LastInsertRowId);
                }
            }
        }

        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sql;
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// /执行查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sql;
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(command);
                    DataTable result = new DataTable();
                    sda.Fill(result);
                    return result;
                }
            }
        }


    }
}
